#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
数据库初始化脚本：创建GitLab、Jenkins和飞书所需的数据库

此脚本会连接MySQL服务器并创建三个单独的数据库：
1. gitlab_data - 存储GitLab项目、提交和用户数据
2. jenkins_data - 存储Jenkins构建数据
3. feishu_data - 存储飞书多维表格数据
"""

import sys
import os
from pathlib import Path

# 添加项目根目录到 Python 路径
project_root = Path(__file__).parent.parent
sys.path.append(str(project_root))

import pymysql
from utils.logger import sync_logger
from config.db_config import DB_BASE_CONFIG, GITLAB_DB_CONFIG, JENKINS_DB_CONFIG, FEISHU_DB_CONFIG

def connect_to_mysql():
    """连接到MySQL服务器（不指定数据库）"""
    try:
        # 创建数据库时不指定具体数据库名
        connect_config = {
            'host': DB_BASE_CONFIG['host'],
            'port': DB_BASE_CONFIG['port'],
            'user': DB_BASE_CONFIG['user'],
            'password': DB_BASE_CONFIG['password']
        }
        
        connection = pymysql.connect(**connect_config)
        sync_logger.log_info("成功连接到MySQL服务器")
        return connection
    except Exception as e:
        sync_logger.log_error(f"连接MySQL服务器失败: {e}")
        raise

def create_database(connection, db_name):
    """创建数据库"""
    try:
        with connection.cursor() as cursor:
            # 检查数据库是否已存在
            cursor.execute(f"SHOW DATABASES LIKE '{db_name}'")
            exists = cursor.fetchone() is not None
            
            if exists:
                sync_logger.log_info(f"数据库 {db_name} 已存在")
                return
                
            # 创建数据库
            cursor.execute(f"CREATE DATABASE IF NOT EXISTS `{db_name}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
            sync_logger.log_info(f"成功创建数据库 {db_name}")
            
            # 授权用户访问该数据库
            cursor.execute(f"GRANT ALL PRIVILEGES ON `{db_name}`.* TO '{DB_BASE_CONFIG['user']}'@'%'")
            cursor.execute("FLUSH PRIVILEGES")
            sync_logger.log_info(f"已授权用户 {DB_BASE_CONFIG['user']} 访问数据库 {db_name}")
    except Exception as e:
        sync_logger.log_error(f"创建数据库 {db_name} 失败: {e}")
        raise

def init_gitlab_tables(connection):
    """初始化GitLab数据库表"""
    try:
        with connection.cursor() as cursor:
            # 切换到GitLab数据库
            cursor.execute(f"USE `{GITLAB_DB_CONFIG['database']}`")
            
            # 创建projects表
            cursor.execute("""
            CREATE TABLE IF NOT EXISTS projects (
                id INT PRIMARY KEY,
                name VARCHAR(255),
                description TEXT,
                created_at DATETIME,
                updated_at DATETIME,
                last_activity_at DATETIME,
                visibility VARCHAR(50)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """)
            
            # 创建commits表
            cursor.execute("""
            CREATE TABLE IF NOT EXISTS commits (
                id VARCHAR(255) PRIMARY KEY,
                project_id INT,
                author_name VARCHAR(255),
                author_email VARCHAR(255),
                title VARCHAR(255),
                message TEXT,
                created_at DATETIME,
                committed_date DATETIME,
                stats_additions INT,
                stats_deletions INT,
                stats_total INT,
                FOREIGN KEY (project_id) REFERENCES projects(id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """)
            
            # 创建members表
            cursor.execute("""
            CREATE TABLE IF NOT EXISTS members (
                id INT PRIMARY KEY,
                username VARCHAR(255),
                name VARCHAR(255),
                email VARCHAR(255),
                state VARCHAR(50),
                created_at DATETIME,
                last_activity_at DATETIME,
                avatar_url VARCHAR(255),
                web_url VARCHAR(255),
                is_admin BOOLEAN,
                external BOOLEAN,
                organization VARCHAR(255)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """)
            
            # 创建project_members表
            cursor.execute("""
            CREATE TABLE IF NOT EXISTS project_members (
                id INT AUTO_INCREMENT PRIMARY KEY,
                project_id INT,
                user_id INT,
                access_level INT,
                created_at DATETIME,
                expires_at DATETIME,
                FOREIGN KEY (project_id) REFERENCES projects(id),
                FOREIGN KEY (user_id) REFERENCES members(id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """)
            
            # 创建开发者统计表
            cursor.execute("""
            CREATE TABLE IF NOT EXISTS developer_statistics (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                username VARCHAR(255),
                project_id INT NOT NULL,
                commit_count INT DEFAULT 0,
                mr_created_count INT DEFAULT 0,
                mr_merged_count INT DEFAULT 0,
                mr_reviewed_count INT DEFAULT 0,
                issue_closed_count INT DEFAULT 0,
                review_pass_rate FLOAT DEFAULT 0.0,
                coverage FLOAT DEFAULT 0.0,
                stats_date DATE NOT NULL,
                UNIQUE KEY unique_user_project_date (user_id, project_id, stats_date)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """)
            
            sync_logger.log_info("GitLab数据库表初始化成功")
    except Exception as e:
        sync_logger.log_error(f"初始化GitLab数据库表失败: {e}")
        raise

def init_jenkins_tables(connection):
    """初始化Jenkins数据库表"""
    try:
        with connection.cursor() as cursor:
            # 切换到Jenkins数据库
            cursor.execute(f"USE `{JENKINS_DB_CONFIG['database']}`")
            
            # 创建jenkins_builds表
            cursor.execute("""
            CREATE TABLE IF NOT EXISTS jenkins_builds (
                id INT AUTO_INCREMENT PRIMARY KEY,
                job_name VARCHAR(255) NOT NULL COMMENT 'Jenkins任务名称',
                build_number INT NOT NULL COMMENT '构建编号',
                status VARCHAR(50) COMMENT '构建状态',
                duration INT COMMENT '构建耗时（秒）',
                timestamp DATETIME COMMENT '构建开始时间',
                git_commit VARCHAR(255) COMMENT '关联的Git提交ID',
                triggered_by VARCHAR(255) COMMENT '触发人/触发方式',
                parameters TEXT COMMENT '构建参数',
                console_log_url VARCHAR(255) COMMENT '日志链接',
                UNIQUE KEY unique_job_build (job_name, build_number)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Jenkins构建记录表'
            """)
            
            # 创建Jenkins任务统计表
            cursor.execute("""
            CREATE TABLE IF NOT EXISTS jenkins_job_stats (
                id INT AUTO_INCREMENT PRIMARY KEY,
                job_name VARCHAR(255) NOT NULL COMMENT 'Jenkins任务名称',
                total_builds INT DEFAULT 0 COMMENT '总构建次数',
                success_builds INT DEFAULT 0 COMMENT '成功构建次数',
                fail_builds INT DEFAULT 0 COMMENT '失败构建次数',
                avg_duration INT DEFAULT 0 COMMENT '平均构建耗时（秒）',
                last_build_status VARCHAR(50) COMMENT '最后一次构建状态',
                last_build_time DATETIME COMMENT '最后一次构建时间',
                success_rate FLOAT DEFAULT 0.0 COMMENT '成功率',
                stats_date DATE NOT NULL COMMENT '统计日期',
                UNIQUE KEY unique_job_date (job_name, stats_date)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Jenkins任务统计表'
            """)
            
            sync_logger.log_info("Jenkins数据库表初始化成功")
    except Exception as e:
        sync_logger.log_error(f"初始化Jenkins数据库表失败: {e}")
        raise
        
def init_feishu_tables(connection):
    """初始化飞书数据库基础表"""
    try:
        with connection.cursor() as cursor:
            # 切换到飞书数据库
            cursor.execute(f"USE `{FEISHU_DB_CONFIG['database']}`")
            
            # 创建飞书同步状态表
            cursor.execute("""
            CREATE TABLE IF NOT EXISTS feishu_sync_status (
                id INT AUTO_INCREMENT PRIMARY KEY,
                last_sync_time DATETIME NOT NULL,
                sync_count INT DEFAULT 0,
                status VARCHAR(50) DEFAULT 'success'
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """)
            
            sync_logger.log_info("飞书数据库基础表初始化成功")
    except Exception as e:
        sync_logger.log_error(f"初始化飞书数据库基础表失败: {e}")
        raise

def main():
    """主函数"""
    connection = None
    try:
        sync_logger.log_info("开始初始化数据库")
        
        # 连接到MySQL服务器
        connection = connect_to_mysql()
        
        # 创建GitLab数据库
        create_database(connection, GITLAB_DB_CONFIG['database'])
        
        # 创建Jenkins数据库
        create_database(connection, JENKINS_DB_CONFIG['database'])
        
        # 创建飞书数据库
        create_database(connection, FEISHU_DB_CONFIG['database'])
        
        # 初始化GitLab表
        init_gitlab_tables(connection)
        
        # 初始化Jenkins表
        init_jenkins_tables(connection)
        
        # 初始化飞书表
        init_feishu_tables(connection)
        
        sync_logger.log_info("数据库初始化完成")
        return 0
    except Exception as e:
        sync_logger.log_error(f"数据库初始化失败: {e}")
        return 1
    finally:
        if connection:
            connection.close()

if __name__ == "__main__":
    sys.exit(main()) 